<!DOCTYPE html>
<html lang=zh-CN>
<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <meta property="og:description" content="伍一的笔记，Kool&#39;s Blog">
    <meta property="og:type" content="website">
    <meta name="description" content="伍一的笔记，Kool&#39;s Blog">
    <meta name="keyword"  content="伍一,伍一的博客">
    <link rel="shortcut icon" href="/img/favicon.ico">

    <title>
        
        ORA-01653,ORACLE表空间文件达到满值 - 伍一的笔记 | Kool&#39;s Blog
        
    </title>

    <!-- Custom CSS -->
    
<link rel="stylesheet" href="/css/aircloud.css">

    
<link rel="stylesheet" href="/css/gitment.css">

    <!--<link rel="stylesheet" href="https://imsun.github.io/gitment/style/default.css">-->
    <link href="//at.alicdn.com/t/font_620856_28hi1hpxx24.css" rel="stylesheet" type="text/css">
    <!-- ga & ba script hoook -->
    <script></script>

    







  
  
  
    <script type="text/javascript">
      var _hmt = _hmt || []
      ;(function () {
        var hm = document.createElement('script')
        hm.src = 'https://hm.baidu.com/hm.js?7e771422dc6f742629fa26747480f822'
        var s = document.getElementsByTagName('script')[0]
        s.parentNode.insertBefore(hm, s)
      })()
    </script>
  



<meta name="generator" content="Hexo 6.3.0"></head>

<body>

<div class="site-nav-toggle" id="site-nav-toggle">
    <button>
        <span class="btn-bar"></span>
        <span class="btn-bar"></span>
        <span class="btn-bar"></span>
    </button>
</div>

<div class="index-about">
    <i> 爱生活爱编码 </i>
</div>

<div class="index-container">
    
    <div class="index-left">
        
<div class="nav" id="nav">
    <div class="avatar-name">
        <div class="avatar radius">
            <img src="/img/zkool.png" />
        </div>
        <div class="name">
            <i>伍一</i>
        </div>
    </div>
    <div class="contents" id="nav-content">
        <ul>
            <li >
                <a href="/">
                    <i class="iconfont icon-shouye1"></i>
                    <span>主页</span>
                </a>
            </li>
            <li >
                <a href="/html/scanner/index.html">
                    <i class="iconfont icon-guidang2"></i>
                    <span>H5条码枪</span>
                </a>
            </li>
            <li >
                <a href="/html/udi/index.html">
                    <i class="iconfont icon-sousuo1"></i>
                    <span>UDI</span>
                </a>
            </li>
            <li >
                <a href="/tags">
                    <i class="iconfont icon-biaoqian1"></i>
                    <span>标签</span>
                </a>
            </li>
            <li >
                <a href="/archives">
                    <i class="iconfont icon-guidang2"></i>
                    <span>存档</span>
                </a>
            </li>
            <li >
                <a href="/collect/">
                    <i class="iconfont icon-shoucang1"></i>
                    <span>收藏</span>
                </a>
            </li>
            <li >
                <a href="/about/">
                    <i class="iconfont icon-guanyu2"></i>
                    <span>关于</span>
                </a>
            </li>
            
            <li>
                <a id="search">
                    <i class="iconfont icon-sousuo1"></i>
                    <span>搜索</span>
                </a>
            </li>
            
        </ul>
    </div>
    
        <div id="toc" class="toc-article">
    <ol class="toc"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E8%A7%A3%E9%87%8A%E8%AF%B4%E6%98%8E"><span class="toc-text">解释说明</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%A4%84%E7%90%86%E6%AD%A5%E9%AA%A4"><span class="toc-text">处理步骤</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#1-%E8%BF%9E%E4%B8%8A%E6%95%B0%E6%8D%AE%E5%BA%93%E5%90%8E%E6%9F%A5%E7%9C%8B%E8%A1%A8%E7%A9%BA%E9%97%B4%E6%83%85%E5%86%B5"><span class="toc-text">1. 连上数据库后查看表空间情况</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#2-SYSTEM%E8%A1%A8%E7%A9%BA%E9%97%B4%E4%B8%8D%E8%B6%B3%EF%BC%8C%E6%9F%A5%E7%9C%8B%E8%A1%A8%E7%A9%BA%E9%97%B4%E6%96%87%E4%BB%B6%E5%90%8D%E7%A7%B0%E5%92%8C%E6%98%AF%E5%90%A6%E8%87%AA%E5%8A%A8%E5%A2%9E%E9%95%BF"><span class="toc-text">2. SYSTEM表空间不足，查看表空间文件名称和是否自动增长</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#3-%E5%BD%93%E8%A1%A8%E7%A9%BA%E9%97%B4%E4%B8%8D%E8%B6%B3%E6%97%B6"><span class="toc-text">3. 当表空间不足时</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#4-%E5%88%A0%E9%99%A4%E8%A1%A8%E7%A9%BA%E9%97%B4%E6%96%87%E4%BB%B6"><span class="toc-text">4. 删除表空间文件</span></a></li></ol></li></ol>
</div>
    
</div>


<div class="search-field" id="search-field">
    <div class="search-bg" id="search-bg"></div>
    <div class="search-container">
        <div class="search-input">
            <span id="esc-search"> <i class="icon-fanhui iconfont"></i></span>
            <input id="search-input"/>
            <span id="begin-search">搜索</span>
        </div>
        <div class="search-result-container" id="search-result-container">

        </div>
    </div>
</div>

        <div class="index-about-mobile">
            <i> 爱生活爱编码 </i>
        </div>
    </div>
    
    <div class="index-middle">
        <!-- Main Content -->
        


<div class="post-container">
    <div class="post-title">
        ORA-01653,ORACLE表空间文件达到满值
    </div>

    <div class="post-meta">
        <span class="attr">发布于：<span>2019-12-20 08:30:35</span></span>
        
        <span class="attr">标签：/
        
        <a class="tag" href="/tags/#Oracle" title="Oracle">Oracle</a>
        <span>/</span>
        
        
        </span>
        <span class="attr">访问：<span id="busuanzi_value_page_pv"></span>
</span>
</span>
    </div>
    <div class="post-content ">
        <p>操作数据库出现错误，ORA-01653，ORA-00604: 8192</p>
<h3 id="解释说明"><a href="#解释说明" class="headerlink" title="解释说明"></a>解释说明</h3><p>由于Oracle的Rowid使用22位来代表数据块号，因此Oracle表空间数据文件每个数据文件最多只能包含2^22个数据块。也因此数据库表空间的数据文件不是无限增长的，例如：在数据块为8k的情况下，单个数据文件的最大容量为8K×2^22 &#x3D; 32G。同理，数据块为2K，数据文件最大约8G，数据块为32，数据文件最大约16×8G，故若达到这个最大容量之后，则即便是设置了自增长，也不是无限自增长，此时则需要为这个表空间添加数据文件。</p>
<h3 id="处理步骤"><a href="#处理步骤" class="headerlink" title="处理步骤"></a>处理步骤</h3><h4 id="1-连上数据库后查看表空间情况"><a href="#1-连上数据库后查看表空间情况" class="headerlink" title="1. 连上数据库后查看表空间情况"></a>1. 连上数据库后查看表空间情况</h4><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br></pre></td><td class="code"><pre><span class="line">SELECT UPPER(F.TABLESPACE_NAME) &quot;表空间名&quot;,</span><br><span class="line">       D.TOT_GROOTTE_MB &quot;表空间大小(M)&quot;,</span><br><span class="line">       D.TOT_GROOTTE_MB - F.TOTAL_BYTES &quot;已使用空间(M)&quot;,</span><br><span class="line">       TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,</span><br><span class="line">                     2),</span><br><span class="line">               &#x27;990.99&#x27;) &quot;使用比&quot;,</span><br><span class="line">       F.TOTAL_BYTES &quot;空闲空间(M)&quot;,</span><br><span class="line">       F.MAX_BYTES &quot;最大块(M)&quot;</span><br><span class="line">  FROM (SELECT TABLESPACE_NAME,</span><br><span class="line">               ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,</span><br><span class="line">               ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES</span><br><span class="line">          FROM SYS.DBA_FREE_SPACE</span><br><span class="line">         GROUP BY TABLESPACE_NAME) F,</span><br><span class="line">       (SELECT DD.TABLESPACE_NAME,</span><br><span class="line">               ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB</span><br><span class="line">          FROM SYS.DBA_DATA_FILES DD</span><br><span class="line">         GROUP BY DD.TABLESPACE_NAME) D</span><br><span class="line"> WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME</span><br><span class="line"> ORDER BY 4 DESC;</span><br></pre></td></tr></table></figure>

<h4 id="2-SYSTEM表空间不足，查看表空间文件名称和是否自动增长"><a href="#2-SYSTEM表空间不足，查看表空间文件名称和是否自动增长" class="headerlink" title="2. SYSTEM表空间不足，查看表空间文件名称和是否自动增长"></a>2. SYSTEM表空间不足，查看表空间文件名称和是否自动增长</h4><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLE FROM dba_data_files;</span><br></pre></td></tr></table></figure>

<h4 id="3-当表空间不足时"><a href="#3-当表空间不足时" class="headerlink" title="3. 当表空间不足时"></a>3. 当表空间不足时</h4><ul>
<li>扩展表空间，当表空间文件本身较小时，可以选择扩大表空间文件的大小，sql语句：</li>
</ul>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">alter database datafile &#x27;D:\ORACLE\PRODUCT\ORADATA\TEST\USERS01.DBF&#x27; resize 50m;</span><br></pre></td></tr></table></figure>

<ul>
<li>设置文件的自动增长，首先选择设置自增长，sql语句：</li>
</ul>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">ALTER DATABASE DATAFILE &#x27;E:\APP\ZHUANGB1\ORADATA\WHU\USERS01.DBF&#x27; AUTOEXTEND  ON NEXT  400M MAXSIZE UNLIMITED;</span><br></pre></td></tr></table></figure>

<ul>
<li>增加数据文件，在自增长失灵了之后，需要增加数据文件，sql语句：</li>
</ul>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">Alter tablespace 表空间名 add datafile ‘数据文件存放的路径’ size 数据文件大小M autoextend on next 每次自增长大小M Maxsize UNLIMITED；　　</span><br><span class="line">//示例：</span><br><span class="line">alter tablespace USER add datafile &#x27;E:\app\EmmaXu\product\11.1.0\db_1\database\SDE_1.dbf&#x27; size 400M autoextend off</span><br></pre></td></tr></table></figure>

<h4 id="4-删除表空间文件"><a href="#4-删除表空间文件" class="headerlink" title="4. 删除表空间文件"></a>4. 删除表空间文件</h4><ul>
<li>有时表空间文件添加错误，需要删除重新添加，sql语句：</li>
</ul>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">alter tablespace WEBS_TABLESPACE drop datafile &#x27;F:\temp\USERS02.DBF&#x27;;</span><br></pre></td></tr></table></figure>
        
            <div class="donate-container">
    <div class="donate-button">
        <button id="donate-button">赞赏</button>
    </div>
    <div class="donate-img-container hide" id="donate-img-container">
        <img id="donate-img" src="" data-src="/img/my_wechat.jpg">
        <p> 感谢鼓励 </p>
    </div>
</div>
        
        <br />
        <div id="comment-container">
        </div>
        <div id="disqus_thread"></div>
        <div id="lv-container"></div>
        <div class="giscus"></div>
    </div>
</div>

    </div>
</div>


<footer class="footer">
    <ul class="list-inline text-center">
        
        

        
        <li>
            <a target="_blank" href="http://weibo.com/zha0ku1">
                            <span class="fa-stack fa-lg">
                                  <i class="iconfont icon-weibo"></i>
                            </span>
            </a>
        </li>
        

        

        
        <li>
            <a target="_blank"  href="https://github.com/zkool">
                            <span class="fa-stack fa-lg">
                                <i class="iconfont icon-github"></i>
                            </span>
            </a>
        </li>
        

        

    </ul>
    
    <p>
        <span>/</span>
        
        <span><a target="_blank" rel="noopener" href="https://niexiaotao.com">Xiaotao&#39;s Page</a></span>
        <span>/</span>
        
        <span><a href="#">It helps SEO</a></span>
        <span>/</span>
        
    </p>
    
    <p>
        <span id="busuanzi_container_site_pv">
            <span id="busuanzi_value_site_pv"></span>PV
        </span>
        <span id="busuanzi_container_site_uv">
            <span id="busuanzi_value_site_uv"></span>UV
        </span>
        Created By <a target="_blank" rel="noopener" href="https://hexo.io/">Hexo</a>  Theme <a target="_blank" rel="noopener" href="https://github.com/aircloud/hexo-theme-aircloud">AirCloud</a></p>
</footer>




</body>

<script>
    // We expose some of the variables needed by the front end
    window.hexo_search_path = "search.json"
    window.hexo_root = "/"
    window.isPost = true
</script>
<script src="https://cdn.bootcss.com/jquery/3.3.1/jquery.min.js"></script>

<script src="/js/index.js"></script>

<script async src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>






</html>
